{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This file is used for processing of data to bring it in the final shape."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "IPython.notebook.set_autosave_interval(60000)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Autosaving every 60 seconds\n"
     ]
    }
   ],
   "source": [
    "%autosave 60"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import time\n",
    "from datetime import datetime\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "\n",
    "import google.datalab.storage as storage\n",
    "from io import BytesIO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "BUCKET_NAME = \"msil_raw\"\n",
    "FOLDER_NAME = \"training_data\"\n",
    "FILE_NAME = \"msil_data.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# setting up the parameters\n",
    "plt.rcParams[\"figure.figsize\"] = (10, 10)\n",
    "pd.set_option(\"display.max_rows\", 200)\n",
    "pd.set_option(\"display.max_columns\", 200)\n",
    "pd.set_option(\"precision\", 15)\n",
    "sns.set_style(\"darkgrid\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>IMEI</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Minute</th>\n",
       "      <th>Seconds</th>\n",
       "      <th>tp</th>\n",
       "      <th>sp</th>\n",
       "      <th>EVVSP</th>\n",
       "      <th>EVGPO</th>\n",
       "      <th>EVOAS</th>\n",
       "      <th>EVIGM_Latest</th>\n",
       "      <th>EVCOM_Latest</th>\n",
       "      <th>EVACO_Z</th>\n",
       "      <th>EVIRT_Min</th>\n",
       "      <th>EVDI2</th>\n",
       "      <th>EVBMI_Latest</th>\n",
       "      <th>EVBMA_Latest</th>\n",
       "      <th>EVVAC</th>\n",
       "      <th>EVODO</th>\n",
       "      <th>EVODOH</th>\n",
       "      <th>EVSMA_EWMA</th>\n",
       "      <th>EVSMA_delta</th>\n",
       "      <th>Driver name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>352891066262722.0</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-7.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OMKANWAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>352891066262722.0</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OMKANWAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>352891066262722.0</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OMKANWAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>352891066262722.0</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OMKANWAR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>352891066262722.0</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>22</td>\n",
       "      <td>1</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>OMKANWAR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  Unnamed: 0.1               IMEI  Year  Month  Day  Hour  \\\n",
       "0           0             0  352891066262722.0  2018     11   17     8   \n",
       "1           1             1  352891066262722.0  2018     11   17     8   \n",
       "2           2             2  352891066262722.0  2018     11   17     8   \n",
       "3           3             3  352891066262722.0  2018     11   17     8   \n",
       "4           4             4  352891066262722.0  2018     11   17     8   \n",
       "\n",
       "   Minute  Seconds  tp      sp  EVVSP  EVGPO  EVOAS  EVIGM_Latest  \\\n",
       "0      39       18   1     0.0    0.0     10   19.0            16   \n",
       "1      39       19   1  1000.0    0.0     10   19.0            17   \n",
       "2      39       20   1  2000.0    0.0     10   19.0            17   \n",
       "3      39       21   1  3000.0    0.0     10   19.0            17   \n",
       "4      39       22   1  4000.0    0.0     10   19.0            17   \n",
       "\n",
       "   EVCOM_Latest  EVACO_Z  EVIRT_Min  EVDI2  EVBMI_Latest  EVBMA_Latest  EVVAC  \\\n",
       "0            17     -7.0        213     16          20.0          21.5    0.0   \n",
       "1            17     -5.0         17     17          20.0          21.5    0.0   \n",
       "2            17     -8.0         17     17          20.0          21.5    0.0   \n",
       "3            17     -9.0         17     17          20.0          21.5    0.0   \n",
       "4            17     -4.0         17     17          20.0          21.5    0.0   \n",
       "\n",
       "   EVODO  EVODOH  EVSMA_EWMA  EVSMA_delta Driver name  \n",
       "0    688     0.0        93.0          0.0    OMKANWAR  \n",
       "1    688     0.0        93.0          0.0    OMKANWAR  \n",
       "2    688     0.0        93.0          0.0    OMKANWAR  \n",
       "3    688     0.0        93.0          0.0    OMKANWAR  \n",
       "4    688     0.0        93.0          0.0    OMKANWAR  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mybucket = storage.Bucket(BUCKET_NAME)\n",
    "data_csv = mybucket.object(FOLDER_NAME + \"/\" + FILE_NAME)\n",
    "\n",
    "uri = data_csv.uri\n",
    "%gcs read --object $uri --variable data\n",
    "\n",
    "data = pd.read_csv(BytesIO(data))\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# getting the unique IMEIs from the dataset\n",
    "imei_list = list(data[\"IMEI\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# encoding the IMEI number\n",
    "data[\"IMEI\"] = data[\"IMEI\"].map({\n",
    "  352891066262722.0:1,\n",
    "  352891066263282.0:2,\n",
    "  358272088699007.0:3,\n",
    "  358272088699072.0:4,\n",
    "  358272088709954.0:5,\n",
    "  358272088715043.0:6,\n",
    "  358272088715191.0:7,\n",
    "  358272088716215.0:8,\n",
    "  358272088718575.0:9\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['OMKANWAR',\n",
       " 'NONAME',\n",
       " 'GOVIND',\n",
       " 'NAVDEEP',\n",
       " 'MAHINDER',\n",
       " 'DEEPAK',\n",
       " 'KAMAL',\n",
       " 'NAVEEN',\n",
       " 'AMITKUMAR',\n",
       " 'MOHINDER',\n",
       " 'MANOJ',\n",
       " 'MOHIT',\n",
       " 'MANISH',\n",
       " 'GAJRAJ',\n",
       " 'KULDEEP',\n",
       " 'JAGDISH',\n",
       " 'HIMMATSINGH',\n",
       " 'MAHIPAL',\n",
       " 'ASHISH',\n",
       " 'NITIN',\n",
       " 'VISHAL',\n",
       " 'PRIYANK',\n",
       " 'BHARAT',\n",
       " 'SHASHANK',\n",
       " 'RAJ',\n",
       " 'SRAINA',\n",
       " 'ABHIJIT',\n",
       " 'TARUN',\n",
       " 'PAVAN']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# getting the unique Driver Name from the dataset\n",
    "driver_list = list(data[\"Driver name\"].unique())\n",
    "driver_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# encoding the Driver Name\n",
    "data[\"Driver name\"] = data[\"Driver name\"].map({\n",
    "  'OMKANWAR': 1,\n",
    "  'NONAME': 0,\n",
    "  'GOVIND': 2,\n",
    "  'NAVDEEP': 3,\n",
    "  'MAHINDER': 4,\n",
    "  'DEEPAK': 5,\n",
    "  'KAMAL': 6,\n",
    "  'NAVEEN': 7,\n",
    "  'AMITKUMAR': 8,\n",
    "  'MOHINDER': 9,\n",
    "  'MANOJ': 10,\n",
    "  'MOHIT': 11,\n",
    "  'MANISH': 12,\n",
    "  'GAJRAJ': 13,\n",
    "  'KULDEEP': 14,\n",
    "  'JAGDISH': 15,\n",
    "  'HIMMATSINGH': 16,\n",
    "  'MAHIPAL': 17,\n",
    "  'ASHISH': 18,\n",
    "  'NITIN': 19,\n",
    "  'VISHAL': 20,\n",
    "  'PRIYANK': 21,\n",
    "  'BHARAT': 22,\n",
    "  'SHASHANK': 23,\n",
    "  'RAJ': 24,\n",
    "  'SRAINA': 25,\n",
    "  'ABHIJIT': 26,\n",
    "  'TARUN': 27,\n",
    "  'PAVAN': 28\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# encoding the trip number universally\n",
    "data[\"tp\"] = data[\"IMEI\"].astype(\"str\") + data[\"Year\"].astype(\"str\") + data[\"Month\"].astype(\"str\") + data[\"Day\"].astype(\"str\") + data[\"tp\"].astype(\"str\")\n",
    "\n",
    "encoder = LabelEncoder()\n",
    "data[\"tp\"] = encoder.fit_transform(data[\"tp\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 0.1</th>\n",
       "      <th>IMEI</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Minute</th>\n",
       "      <th>Seconds</th>\n",
       "      <th>tp</th>\n",
       "      <th>sp</th>\n",
       "      <th>EVVSP</th>\n",
       "      <th>EVGPO</th>\n",
       "      <th>EVOAS</th>\n",
       "      <th>EVIGM_Latest</th>\n",
       "      <th>EVCOM_Latest</th>\n",
       "      <th>EVACO_Z</th>\n",
       "      <th>EVIRT_Min</th>\n",
       "      <th>EVDI2</th>\n",
       "      <th>EVBMI_Latest</th>\n",
       "      <th>EVBMA_Latest</th>\n",
       "      <th>EVVAC</th>\n",
       "      <th>EVODO</th>\n",
       "      <th>EVODOH</th>\n",
       "      <th>EVSMA_EWMA</th>\n",
       "      <th>EVSMA_delta</th>\n",
       "      <th>Driver name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1528461</th>\n",
       "      <td>1528550</td>\n",
       "      <td>1528550</td>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>18</td>\n",
       "      <td>45</td>\n",
       "      <td>47</td>\n",
       "      <td>637</td>\n",
       "      <td>3878000.0</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>10</td>\n",
       "      <td>20.0</td>\n",
       "      <td>23</td>\n",
       "      <td>33</td>\n",
       "      <td>-80.0</td>\n",
       "      <td>28</td>\n",
       "      <td>32</td>\n",
       "      <td>32.5</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>8478</td>\n",
       "      <td>21.849188055555555</td>\n",
       "      <td>66.059370436632904</td>\n",
       "      <td>0.002012557173998</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>941620</th>\n",
       "      <td>941679</td>\n",
       "      <td>941679</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "      <td>22</td>\n",
       "      <td>27</td>\n",
       "      <td>26</td>\n",
       "      <td>403</td>\n",
       "      <td>1359000.0</td>\n",
       "      <td>25.914100000000001</td>\n",
       "      <td>11</td>\n",
       "      <td>14.0</td>\n",
       "      <td>26</td>\n",
       "      <td>30</td>\n",
       "      <td>-106.0</td>\n",
       "      <td>24</td>\n",
       "      <td>28</td>\n",
       "      <td>34.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>11.263055555555557</td>\n",
       "      <td>4143</td>\n",
       "      <td>9.299236791666667</td>\n",
       "      <td>82.104921309470953</td>\n",
       "      <td>0.006946485066806</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4163840</th>\n",
       "      <td>4164109</td>\n",
       "      <td>4212709</td>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>6</td>\n",
       "      <td>15</td>\n",
       "      <td>18</td>\n",
       "      <td>29</td>\n",
       "      <td>1822</td>\n",
       "      <td>20222000.0</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>10</td>\n",
       "      <td>24.5</td>\n",
       "      <td>34</td>\n",
       "      <td>28</td>\n",
       "      <td>36.0</td>\n",
       "      <td>32</td>\n",
       "      <td>27</td>\n",
       "      <td>30.0</td>\n",
       "      <td>31.5</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>1317</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>26.881436573162730</td>\n",
       "      <td>0.024358082265667</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1990646</th>\n",
       "      <td>1990735</td>\n",
       "      <td>1990735</td>\n",
       "      <td>3</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "      <td>13</td>\n",
       "      <td>876</td>\n",
       "      <td>164000.0</td>\n",
       "      <td>23.851600000000001</td>\n",
       "      <td>11</td>\n",
       "      <td>19.5</td>\n",
       "      <td>23</td>\n",
       "      <td>31</td>\n",
       "      <td>-156.0</td>\n",
       "      <td>29</td>\n",
       "      <td>28</td>\n",
       "      <td>22.5</td>\n",
       "      <td>24.0</td>\n",
       "      <td>-0.043333333333331</td>\n",
       "      <td>4068</td>\n",
       "      <td>0.615307083333334</td>\n",
       "      <td>17.072967269488100</td>\n",
       "      <td>0.002473466762311</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>805956</th>\n",
       "      <td>806015</td>\n",
       "      <td>806015</td>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>19</td>\n",
       "      <td>14</td>\n",
       "      <td>16</td>\n",
       "      <td>534</td>\n",
       "      <td>6395000.0</td>\n",
       "      <td>45.156199999999998</td>\n",
       "      <td>11</td>\n",
       "      <td>21.5</td>\n",
       "      <td>28</td>\n",
       "      <td>36</td>\n",
       "      <td>-127.0</td>\n",
       "      <td>30</td>\n",
       "      <td>34</td>\n",
       "      <td>34.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>-1.150277777777791</td>\n",
       "      <td>3363</td>\n",
       "      <td>19.662119722222226</td>\n",
       "      <td>70.636010724218210</td>\n",
       "      <td>0.004610533024348</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210490</th>\n",
       "      <td>210528</td>\n",
       "      <td>210528</td>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>11</td>\n",
       "      <td>10</td>\n",
       "      <td>43</td>\n",
       "      <td>14</td>\n",
       "      <td>67</td>\n",
       "      <td>948000.0</td>\n",
       "      <td>1.515600000000000</td>\n",
       "      <td>11</td>\n",
       "      <td>18.5</td>\n",
       "      <td>26</td>\n",
       "      <td>38</td>\n",
       "      <td>84.0</td>\n",
       "      <td>30</td>\n",
       "      <td>30</td>\n",
       "      <td>21.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>4.210000000000000</td>\n",
       "      <td>2457</td>\n",
       "      <td>10.002315861111107</td>\n",
       "      <td>59.752105782364062</td>\n",
       "      <td>0.001766297707256</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2616959</th>\n",
       "      <td>2617092</td>\n",
       "      <td>2617092</td>\n",
       "      <td>4</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>7</td>\n",
       "      <td>15</td>\n",
       "      <td>53</td>\n",
       "      <td>31</td>\n",
       "      <td>1335</td>\n",
       "      <td>591000.0</td>\n",
       "      <td>30.062500000000000</td>\n",
       "      <td>11</td>\n",
       "      <td>23.5</td>\n",
       "      <td>32</td>\n",
       "      <td>48</td>\n",
       "      <td>407.0</td>\n",
       "      <td>36</td>\n",
       "      <td>36</td>\n",
       "      <td>32.5</td>\n",
       "      <td>34.0</td>\n",
       "      <td>-4.361944444444443</td>\n",
       "      <td>3631</td>\n",
       "      <td>5.183782708333332</td>\n",
       "      <td>74.850604424225551</td>\n",
       "      <td>0.001715404211026</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>446435</th>\n",
       "      <td>446484</td>\n",
       "      <td>446484</td>\n",
       "      <td>1</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>15</td>\n",
       "      <td>19</td>\n",
       "      <td>23</td>\n",
       "      <td>201</td>\n",
       "      <td>2244000.0</td>\n",
       "      <td>5.296900000000000</td>\n",
       "      <td>11</td>\n",
       "      <td>25.0</td>\n",
       "      <td>31</td>\n",
       "      <td>38</td>\n",
       "      <td>108.0</td>\n",
       "      <td>39</td>\n",
       "      <td>35</td>\n",
       "      <td>21.5</td>\n",
       "      <td>23.5</td>\n",
       "      <td>1.649444444444444</td>\n",
       "      <td>4471</td>\n",
       "      <td>16.792356763888890</td>\n",
       "      <td>84.536393250630780</td>\n",
       "      <td>0.004623500021381</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2206868</th>\n",
       "      <td>2206957</td>\n",
       "      <td>2206957</td>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>15</td>\n",
       "      <td>11</td>\n",
       "      <td>26</td>\n",
       "      <td>964</td>\n",
       "      <td>436000.0</td>\n",
       "      <td>24.140599999999999</td>\n",
       "      <td>11</td>\n",
       "      <td>21.5</td>\n",
       "      <td>31</td>\n",
       "      <td>39</td>\n",
       "      <td>-176.0</td>\n",
       "      <td>40</td>\n",
       "      <td>41</td>\n",
       "      <td>20.0</td>\n",
       "      <td>22.5</td>\n",
       "      <td>0.824444444444437</td>\n",
       "      <td>6534</td>\n",
       "      <td>1.614173055555556</td>\n",
       "      <td>89.266639360698946</td>\n",
       "      <td>0.002258961379624</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1963521</th>\n",
       "      <td>1963610</td>\n",
       "      <td>1963610</td>\n",
       "      <td>3</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>16</td>\n",
       "      <td>33</td>\n",
       "      <td>3</td>\n",
       "      <td>868</td>\n",
       "      <td>579000.0</td>\n",
       "      <td>36.812500000000000</td>\n",
       "      <td>11</td>\n",
       "      <td>20.0</td>\n",
       "      <td>25</td>\n",
       "      <td>42</td>\n",
       "      <td>-104.0</td>\n",
       "      <td>33</td>\n",
       "      <td>30</td>\n",
       "      <td>29.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>-11.545277777777786</td>\n",
       "      <td>3771</td>\n",
       "      <td>8.200991847222221</td>\n",
       "      <td>47.659677542445593</td>\n",
       "      <td>0.005412798049008</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Unnamed: 0  Unnamed: 0.1  IMEI  Year  Month  Day  Hour  Minute  \\\n",
       "1528461     1528550       1528550     2  2019      1   17    18      45   \n",
       "941620       941679        941679     2  2018     12   17    22      27   \n",
       "4163840     4164109       4212709     7  2018     12    6    15      18   \n",
       "1990646     1990735       1990735     3  2018     12   14    11      15   \n",
       "805956       806015        806015     2  2018     12    4    19      14   \n",
       "210490       210528        210528     1  2018     12   11    10      43   \n",
       "2616959     2617092       2617092     4  2018     12    7    15      53   \n",
       "446435       446484        446484     1  2019      1   11    15      19   \n",
       "2206868     2206957       2206957     3  2019      1   12    15      11   \n",
       "1963521     1963610       1963610     3  2018     12   12    16      33   \n",
       "\n",
       "         Seconds    tp          sp               EVVSP  EVGPO  EVOAS  \\\n",
       "1528461       47   637   3878000.0   0.000000000000000     10   20.0   \n",
       "941620        26   403   1359000.0  25.914100000000001     11   14.0   \n",
       "4163840       29  1822  20222000.0   0.000000000000000     10   24.5   \n",
       "1990646       13   876    164000.0  23.851600000000001     11   19.5   \n",
       "805956        16   534   6395000.0  45.156199999999998     11   21.5   \n",
       "210490        14    67    948000.0   1.515600000000000     11   18.5   \n",
       "2616959       31  1335    591000.0  30.062500000000000     11   23.5   \n",
       "446435        23   201   2244000.0   5.296900000000000     11   25.0   \n",
       "2206868       26   964    436000.0  24.140599999999999     11   21.5   \n",
       "1963521        3   868    579000.0  36.812500000000000     11   20.0   \n",
       "\n",
       "         EVIGM_Latest  EVCOM_Latest  EVACO_Z  EVIRT_Min  EVDI2  EVBMI_Latest  \\\n",
       "1528461            23            33    -80.0         28     32          32.5   \n",
       "941620             26            30   -106.0         24     28          34.0   \n",
       "4163840            34            28     36.0         32     27          30.0   \n",
       "1990646            23            31   -156.0         29     28          22.5   \n",
       "805956             28            36   -127.0         30     34          34.0   \n",
       "210490             26            38     84.0         30     30          21.5   \n",
       "2616959            32            48    407.0         36     36          32.5   \n",
       "446435             31            38    108.0         39     35          21.5   \n",
       "2206868            31            39   -176.0         40     41          20.0   \n",
       "1963521            25            42   -104.0         33     30          29.0   \n",
       "\n",
       "         EVBMA_Latest               EVVAC  EVODO              EVODOH  \\\n",
       "1528461          35.0   0.000000000000000   8478  21.849188055555555   \n",
       "941620           38.0  11.263055555555557   4143   9.299236791666667   \n",
       "4163840          31.5   0.000000000000000   1317   0.000000000000000   \n",
       "1990646          24.0  -0.043333333333331   4068   0.615307083333334   \n",
       "805956           35.0  -1.150277777777791   3363  19.662119722222226   \n",
       "210490           23.0   4.210000000000000   2457  10.002315861111107   \n",
       "2616959          34.0  -4.361944444444443   3631   5.183782708333332   \n",
       "446435           23.5   1.649444444444444   4471  16.792356763888890   \n",
       "2206868          22.5   0.824444444444437   6534   1.614173055555556   \n",
       "1963521          30.0 -11.545277777777786   3771   8.200991847222221   \n",
       "\n",
       "                 EVSMA_EWMA        EVSMA_delta  Driver name  \n",
       "1528461  66.059370436632904  0.002012557173998            3  \n",
       "941620   82.104921309470953  0.006946485066806            3  \n",
       "4163840  26.881436573162730  0.024358082265667           23  \n",
       "1990646  17.072967269488100  0.002473466762311           11  \n",
       "805956   70.636010724218210  0.004610533024348            5  \n",
       "210490   59.752105782364062  0.001766297707256            2  \n",
       "2616959  74.850604424225551  0.001715404211026            8  \n",
       "446435   84.536393250630780  0.004623500021381            2  \n",
       "2206868  89.266639360698946  0.002258961379624           11  \n",
       "1963521  47.659677542445593  0.005412798049008           11  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2122"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[\"tp\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def impute_temp(trip_list):\n",
    "    list_ = []\n",
    "    for i in range(0, len(trip_list)):\n",
    "        temp = trip_list[0]\n",
    "        list_.append(temp)\n",
    "    return list_\n",
    "\n",
    "def impute_temp_df(data, colname):\n",
    "    temp_list = []\n",
    "    for i in list(data[\"tp\"].unique()):\n",
    "        trip_df = data[data[\"tp\"] == i]\n",
    "        trip_list = list(trip_df[colname])\n",
    "        trip_list = impute_temp(trip_list)\n",
    "        temp_list.append(trip_list)\n",
    "\n",
    "    flat_list = []\n",
    "    for sublist in temp_list:\n",
    "        for item in sublist:\n",
    "            flat_list.append(item)\n",
    "    return flat_list\n",
    "  \n",
    "def replace_df(dataframe, attribute_name, list_name):\n",
    "    \"\"\"\n",
    "    inputs - dataframe name, list name (should be of same length), name of the predictor to be replaced\n",
    "    output - dataframe with replaced column\n",
    "    \"\"\"\n",
    "    col_list = list(dataframe.columns)\n",
    "    loc = col_list.index(attribute_name)\n",
    "    dataframe = dataframe.drop(columns = attribute_name)\n",
    "    dataframe.insert(loc, attribute_name, list_name)\n",
    "    return dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rate of change in the dataset\n",
    "def change_rate(feat_list):\n",
    "    cr_list = [0]\n",
    "    cr_list = cr_list*60\n",
    "    for i in range(60, len(feat_list)):\n",
    "        delta = (feat_list[i] - feat_list[i-60])/60\n",
    "        cr_list.append(delta)\n",
    "    return cr_list\n",
    "\n",
    "def change_rate_df(dataframe, feature_name):\n",
    "    change_list = []\n",
    "    change_list_2 = []\n",
    "    debug_0 = []\n",
    "    debug_1 = []\n",
    "    debug_2 = []\n",
    "    for i in list(dataframe[\"tp\"].unique()):\n",
    "        temp_df = dataframe[dataframe[\"tp\"] == i]\n",
    "        #print(i)\n",
    "        temp_list = list(temp_df[feature_name])\n",
    "        cr_frame = change_rate(temp_list)\n",
    "        cr_frame_2 = change_rate(cr_frame)\n",
    "        debug_1.append(len(cr_frame))\n",
    "        debug_2.append(len(temp_df))\n",
    "        debug_0.append(i)\n",
    "        change_list.append(cr_frame)\n",
    "        change_list_2.append(cr_frame_2)\n",
    "  \n",
    "    debug_df = pd.DataFrame({\n",
    "        \"trip number\": debug_0,\n",
    "        \"len list\": debug_1,\n",
    "        \"len trip\": debug_2\n",
    "    })\n",
    "    flat_list_1 = []\n",
    "    for sublist in change_list:\n",
    "        for item in sublist:\n",
    "            flat_list_1.append(item)\n",
    "  \n",
    "    flat_list_2 = []\n",
    "    for sublist in change_list_2:\n",
    "        for item in sublist:\n",
    "            flat_list_2.append(item)\n",
    "    return flat_list_1, flat_list_2\n",
    "    #return debug_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "EVIRT_list = impute_temp_df(data, \"EVIRT_Min\")\n",
    "data = replace_df(data, \"EVIRT_Min\", EVIRT_list)\n",
    "\n",
    "EVDI2_list = impute_temp_df(data, \"EVDI2\")\n",
    "data = replace_df(data, \"EVDI2\", EVDI2_list)\n",
    "\n",
    "EVIGM_list = impute_temp_df(data, \"EVIGM_Latest\")\n",
    "data = replace_df(data, \"EVIGM_Latest\", EVIGM_list)\n",
    "\n",
    "EVCOM_list = impute_temp_df(data, \"EVCOM_Latest\")\n",
    "data = replace_df(data, \"EVCOM_Latest\", EVCOM_list)\n",
    "\n",
    "EVBMI_list = impute_temp_df(data, \"EVBMI_Latest\")\n",
    "data = replace_df(data, \"EVBMI_Latest\", EVBMI_list)\n",
    "\n",
    "EVBMA_list = impute_temp_df(data, \"EVBMA_Latest\")\n",
    "data = replace_df(data, \"EVBMA_Latest\", EVBMA_list)\n",
    "\n",
    "EVSMA_in_list = impute_temp_df(data, \"EVSMA_EWMA\")\n",
    "data.insert(24, \"EVSMA_in\", EVSMA_in_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.drop(columns = [\"Unnamed: 0\", \"Unnamed: 0.1\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IMEI</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Minute</th>\n",
       "      <th>Seconds</th>\n",
       "      <th>tp</th>\n",
       "      <th>sp</th>\n",
       "      <th>EVVSP</th>\n",
       "      <th>EVGPO</th>\n",
       "      <th>EVOAS</th>\n",
       "      <th>EVIGM_Latest</th>\n",
       "      <th>EVCOM_Latest</th>\n",
       "      <th>EVACO_Z</th>\n",
       "      <th>EVIRT_Min</th>\n",
       "      <th>EVDI2</th>\n",
       "      <th>EVBMI_Latest</th>\n",
       "      <th>EVBMA_Latest</th>\n",
       "      <th>EVVAC</th>\n",
       "      <th>EVODO</th>\n",
       "      <th>EVODOH</th>\n",
       "      <th>EVSMA_in</th>\n",
       "      <th>EVSMA_EWMA</th>\n",
       "      <th>EVSMA_delta</th>\n",
       "      <th>Driver name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-7.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   IMEI  Year  Month  Day  Hour  Minute  Seconds  tp      sp  EVVSP  EVGPO  \\\n",
       "0     1  2018     11   17     8      39       18   0     0.0    0.0     10   \n",
       "1     1  2018     11   17     8      39       19   0  1000.0    0.0     10   \n",
       "2     1  2018     11   17     8      39       20   0  2000.0    0.0     10   \n",
       "3     1  2018     11   17     8      39       21   0  3000.0    0.0     10   \n",
       "4     1  2018     11   17     8      39       22   0  4000.0    0.0     10   \n",
       "\n",
       "   EVOAS  EVIGM_Latest  EVCOM_Latest  EVACO_Z  EVIRT_Min  EVDI2  EVBMI_Latest  \\\n",
       "0   19.0            16            17     -7.0        213     16          20.0   \n",
       "1   19.0            16            17     -5.0        213     16          20.0   \n",
       "2   19.0            16            17     -8.0        213     16          20.0   \n",
       "3   19.0            16            17     -9.0        213     16          20.0   \n",
       "4   19.0            16            17     -4.0        213     16          20.0   \n",
       "\n",
       "   EVBMA_Latest  EVVAC  EVODO  EVODOH  EVSMA_in  EVSMA_EWMA  EVSMA_delta  \\\n",
       "0          21.5    0.0    688     0.0      93.0        93.0          0.0   \n",
       "1          21.5    0.0    688     0.0      93.0        93.0          0.0   \n",
       "2          21.5    0.0    688     0.0      93.0        93.0          0.0   \n",
       "3          21.5    0.0    688     0.0      93.0        93.0          0.0   \n",
       "4          21.5    0.0    688     0.0      93.0        93.0          0.0   \n",
       "\n",
       "   Driver name  \n",
       "0            1  \n",
       "1            1  \n",
       "2            1  \n",
       "3            1  \n",
       "4            1  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data[data[\"tp\"] != 1261]\n",
    "data = data[data[\"tp\"] != 1172]\n",
    "data = data[data[\"tp\"] != 1043]\n",
    "data = data[data[\"tp\"] != 266]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IMEI</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Minute</th>\n",
       "      <th>Seconds</th>\n",
       "      <th>tp</th>\n",
       "      <th>sp</th>\n",
       "      <th>EVVSP</th>\n",
       "      <th>EVGPO</th>\n",
       "      <th>EVOAS</th>\n",
       "      <th>EVIGM_Latest</th>\n",
       "      <th>EVCOM_Latest</th>\n",
       "      <th>EVACO_Z</th>\n",
       "      <th>EVIRT_Min</th>\n",
       "      <th>EVDI2</th>\n",
       "      <th>EVBMI_Latest</th>\n",
       "      <th>EVBMA_Latest</th>\n",
       "      <th>EVVAC</th>\n",
       "      <th>EVODO</th>\n",
       "      <th>EVODOH</th>\n",
       "      <th>EVSMA_in</th>\n",
       "      <th>EVSMA_EWMA</th>\n",
       "      <th>EVSMA_delta</th>\n",
       "      <th>Driver name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-7.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   IMEI  Year  Month  Day  Hour  Minute  Seconds  tp      sp  EVVSP  EVGPO  \\\n",
       "0     1  2018     11   17     8      39       18   0     0.0    0.0     10   \n",
       "1     1  2018     11   17     8      39       19   0  1000.0    0.0     10   \n",
       "2     1  2018     11   17     8      39       20   0  2000.0    0.0     10   \n",
       "3     1  2018     11   17     8      39       21   0  3000.0    0.0     10   \n",
       "4     1  2018     11   17     8      39       22   0  4000.0    0.0     10   \n",
       "\n",
       "   EVOAS  EVIGM_Latest  EVCOM_Latest  EVACO_Z  EVIRT_Min  EVDI2  EVBMI_Latest  \\\n",
       "0   19.0            16            17     -7.0        213     16          20.0   \n",
       "1   19.0            16            17     -5.0        213     16          20.0   \n",
       "2   19.0            16            17     -8.0        213     16          20.0   \n",
       "3   19.0            16            17     -9.0        213     16          20.0   \n",
       "4   19.0            16            17     -4.0        213     16          20.0   \n",
       "\n",
       "   EVBMA_Latest  EVVAC  EVODO  EVODOH  EVSMA_in  EVSMA_EWMA  EVSMA_delta  \\\n",
       "0          21.5    0.0    688     0.0      93.0        93.0          0.0   \n",
       "1          21.5    0.0    688     0.0      93.0        93.0          0.0   \n",
       "2          21.5    0.0    688     0.0      93.0        93.0          0.0   \n",
       "3          21.5    0.0    688     0.0      93.0        93.0          0.0   \n",
       "4          21.5    0.0    688     0.0      93.0        93.0          0.0   \n",
       "\n",
       "   Driver name  \n",
       "0            1  \n",
       "1            1  \n",
       "2            1  \n",
       "3            1  \n",
       "4            1  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4764039, 26)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding columns with EVVSP_change, EVVSP_delta, EVVAC_change and EVVAC_delta\n",
    "EVVSP_change, EVVSP_change_rate = change_rate_df(data, \"EVVSP\")\n",
    "EVVAC_change, EVVAC_change_rate = change_rate_df(data, \"EVVAC\")\n",
    "data.insert(9, \"EVVSP_delta\", EVVSP_change)\n",
    "data.insert(10, \"EVVSP_change\", EVVSP_change_rate)\n",
    "data.insert(20, \"EVVAC_delta\", EVVAC_change)\n",
    "data.insert(21, \"EVVAC_change\", EVVAC_change_rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# selecting columns to be used in model training\n",
    "data = data[['IMEI', 'Year', 'Month', 'Day', 'Hour', 'Minute', 'Seconds', \"tp\", 'sp', 'EVVSP', 'EVVSP_delta', 'EVVSP_change', \n",
    "             'EVGPO', 'EVOAS', 'EVIGM_Latest', 'EVCOM_Latest', 'EVACO_Z', 'EVIRT_Min', 'EVDI2', 'EVBMI_Latest', \n",
    "             'EVBMA_Latest', 'EVVAC', 'EVVAC_delta', 'EVVAC_change', 'EVODO', 'EVODOH', \"Driver name\", 'EVSMA_in', \"EVSMA_EWMA\", \"EVSMA_delta\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IMEI</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Minute</th>\n",
       "      <th>Seconds</th>\n",
       "      <th>tp</th>\n",
       "      <th>sp</th>\n",
       "      <th>EVVSP</th>\n",
       "      <th>EVVSP_delta</th>\n",
       "      <th>EVVSP_change</th>\n",
       "      <th>EVGPO</th>\n",
       "      <th>EVOAS</th>\n",
       "      <th>EVIGM_Latest</th>\n",
       "      <th>EVCOM_Latest</th>\n",
       "      <th>EVACO_Z</th>\n",
       "      <th>EVIRT_Min</th>\n",
       "      <th>EVDI2</th>\n",
       "      <th>EVBMI_Latest</th>\n",
       "      <th>EVBMA_Latest</th>\n",
       "      <th>EVVAC</th>\n",
       "      <th>EVVAC_delta</th>\n",
       "      <th>EVVAC_change</th>\n",
       "      <th>EVODO</th>\n",
       "      <th>EVODOH</th>\n",
       "      <th>Driver name</th>\n",
       "      <th>EVSMA_in</th>\n",
       "      <th>EVSMA_EWMA</th>\n",
       "      <th>EVSMA_delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-7.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-5.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-8.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>21</td>\n",
       "      <td>0</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-9.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>8</td>\n",
       "      <td>39</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10</td>\n",
       "      <td>19.0</td>\n",
       "      <td>16</td>\n",
       "      <td>17</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>213</td>\n",
       "      <td>16</td>\n",
       "      <td>20.0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>688</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>93.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   IMEI  Year  Month  Day  Hour  Minute  Seconds  tp      sp  EVVSP  \\\n",
       "0     1  2018     11   17     8      39       18   0     0.0    0.0   \n",
       "1     1  2018     11   17     8      39       19   0  1000.0    0.0   \n",
       "2     1  2018     11   17     8      39       20   0  2000.0    0.0   \n",
       "3     1  2018     11   17     8      39       21   0  3000.0    0.0   \n",
       "4     1  2018     11   17     8      39       22   0  4000.0    0.0   \n",
       "\n",
       "   EVVSP_delta  EVVSP_change  EVGPO  EVOAS  EVIGM_Latest  EVCOM_Latest  \\\n",
       "0          0.0           0.0     10   19.0            16            17   \n",
       "1          0.0           0.0     10   19.0            16            17   \n",
       "2          0.0           0.0     10   19.0            16            17   \n",
       "3          0.0           0.0     10   19.0            16            17   \n",
       "4          0.0           0.0     10   19.0            16            17   \n",
       "\n",
       "   EVACO_Z  EVIRT_Min  EVDI2  EVBMI_Latest  EVBMA_Latest  EVVAC  EVVAC_delta  \\\n",
       "0     -7.0        213     16          20.0          21.5    0.0          0.0   \n",
       "1     -5.0        213     16          20.0          21.5    0.0          0.0   \n",
       "2     -8.0        213     16          20.0          21.5    0.0          0.0   \n",
       "3     -9.0        213     16          20.0          21.5    0.0          0.0   \n",
       "4     -4.0        213     16          20.0          21.5    0.0          0.0   \n",
       "\n",
       "   EVVAC_change  EVODO  EVODOH  Driver name  EVSMA_in  EVSMA_EWMA  EVSMA_delta  \n",
       "0           0.0    688     0.0            1      93.0        93.0          0.0  \n",
       "1           0.0    688     0.0            1      93.0        93.0          0.0  \n",
       "2           0.0    688     0.0            1      93.0        93.0          0.0  \n",
       "3           0.0    688     0.0            1      93.0        93.0          0.0  \n",
       "4           0.0    688     0.0            1      93.0        93.0          0.0  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# replacing and encoding the EVGPO values\n",
    "data[\"EVGPO\"] = data[\"EVGPO\"].map({\n",
    "  10: \"E\",\n",
    "  8: \"E\",\n",
    "  9: \"E\",\n",
    "  -1: \"E\",\n",
    "  1: \"B\"\n",
    "})\n",
    "\n",
    "data[\"EVGPO\"] = data[\"EVGPO\"].fillna(\"E\")\n",
    "data[\"EVGPO\"] = data[\"EVGPO\"].map({\n",
    "  \"E\": 0,\n",
    "  \"B\": 1\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4764039, 30)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# final shape of data\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IMEI</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Minute</th>\n",
       "      <th>Seconds</th>\n",
       "      <th>tp</th>\n",
       "      <th>sp</th>\n",
       "      <th>EVVSP</th>\n",
       "      <th>EVVSP_delta</th>\n",
       "      <th>EVVSP_change</th>\n",
       "      <th>EVGPO</th>\n",
       "      <th>EVOAS</th>\n",
       "      <th>EVIGM_Latest</th>\n",
       "      <th>EVCOM_Latest</th>\n",
       "      <th>EVACO_Z</th>\n",
       "      <th>EVIRT_Min</th>\n",
       "      <th>EVDI2</th>\n",
       "      <th>EVBMI_Latest</th>\n",
       "      <th>EVBMA_Latest</th>\n",
       "      <th>EVVAC</th>\n",
       "      <th>EVVAC_delta</th>\n",
       "      <th>EVVAC_change</th>\n",
       "      <th>EVODO</th>\n",
       "      <th>EVODOH</th>\n",
       "      <th>Driver name</th>\n",
       "      <th>EVSMA_in</th>\n",
       "      <th>EVSMA_EWMA</th>\n",
       "      <th>EVSMA_delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1108580</th>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>25</td>\n",
       "      <td>14</td>\n",
       "      <td>50</td>\n",
       "      <td>57</td>\n",
       "      <td>492</td>\n",
       "      <td>788000.0</td>\n",
       "      <td>13.687500000000000</td>\n",
       "      <td>0.228125000000000</td>\n",
       "      <td>0.003802083333333</td>\n",
       "      <td>0</td>\n",
       "      <td>20.5</td>\n",
       "      <td>26</td>\n",
       "      <td>27</td>\n",
       "      <td>-128.0</td>\n",
       "      <td>-38</td>\n",
       "      <td>24</td>\n",
       "      <td>30.0</td>\n",
       "      <td>31.5</td>\n",
       "      <td>-1.801111111111113</td>\n",
       "      <td>-0.030018518518519</td>\n",
       "      <td>-0.000500308641975</td>\n",
       "      <td>5754</td>\n",
       "      <td>1.360880708333333</td>\n",
       "      <td>3</td>\n",
       "      <td>88.900000000000006</td>\n",
       "      <td>86.984514267030164</td>\n",
       "      <td>0.002864890407807</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2395132</th>\n",
       "      <td>4</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>17</td>\n",
       "      <td>16</td>\n",
       "      <td>54</td>\n",
       "      <td>26</td>\n",
       "      <td>1031</td>\n",
       "      <td>84000.0</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>39</td>\n",
       "      <td>42</td>\n",
       "      <td>-1004.0</td>\n",
       "      <td>215</td>\n",
       "      <td>43</td>\n",
       "      <td>30.0</td>\n",
       "      <td>31.5</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>1891</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>13</td>\n",
       "      <td>72.099999999999994</td>\n",
       "      <td>72.099999999999994</td>\n",
       "      <td>0.000000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3732533</th>\n",
       "      <td>5</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>21</td>\n",
       "      <td>18</td>\n",
       "      <td>43</td>\n",
       "      <td>18</td>\n",
       "      <td>1579</td>\n",
       "      <td>2673000.0</td>\n",
       "      <td>51.359400000000001</td>\n",
       "      <td>0.059766666666667</td>\n",
       "      <td>-0.004754750000000</td>\n",
       "      <td>0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>-30.0</td>\n",
       "      <td>213</td>\n",
       "      <td>22</td>\n",
       "      <td>18.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>3.515555555555553</td>\n",
       "      <td>0.077041666666667</td>\n",
       "      <td>0.003418055555556</td>\n",
       "      <td>2603</td>\n",
       "      <td>14.765663333333334</td>\n",
       "      <td>18</td>\n",
       "      <td>93.200000000000003</td>\n",
       "      <td>81.785821017636010</td>\n",
       "      <td>0.009688848055461</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1950630</th>\n",
       "      <td>3</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>12</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>862</td>\n",
       "      <td>648000.0</td>\n",
       "      <td>17.468800000000002</td>\n",
       "      <td>0.291146666666667</td>\n",
       "      <td>0.004852444444444</td>\n",
       "      <td>0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>15</td>\n",
       "      <td>16</td>\n",
       "      <td>-4.0</td>\n",
       "      <td>-38</td>\n",
       "      <td>15</td>\n",
       "      <td>16.5</td>\n",
       "      <td>17.5</td>\n",
       "      <td>0.217222222222229</td>\n",
       "      <td>0.003620370370370</td>\n",
       "      <td>0.000060339506173</td>\n",
       "      <td>3613</td>\n",
       "      <td>1.358151055555555</td>\n",
       "      <td>11</td>\n",
       "      <td>91.900000000000006</td>\n",
       "      <td>88.567376611587264</td>\n",
       "      <td>0.002283952935173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>699355</th>\n",
       "      <td>2</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>30</td>\n",
       "      <td>7</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>360</td>\n",
       "      <td>1913000.0</td>\n",
       "      <td>53.890599999999999</td>\n",
       "      <td>0.750130000000000</td>\n",
       "      <td>0.012875444444444</td>\n",
       "      <td>0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>-108.0</td>\n",
       "      <td>-38</td>\n",
       "      <td>20</td>\n",
       "      <td>21.5</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.041666666666667</td>\n",
       "      <td>0.089699074074074</td>\n",
       "      <td>0.002061651234568</td>\n",
       "      <td>2778</td>\n",
       "      <td>13.167723305555556</td>\n",
       "      <td>3</td>\n",
       "      <td>92.000000000000000</td>\n",
       "      <td>79.504335189671053</td>\n",
       "      <td>0.003536786090535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3127675</th>\n",
       "      <td>4</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>26</td>\n",
       "      <td>16</td>\n",
       "      <td>56</td>\n",
       "      <td>45</td>\n",
       "      <td>1289</td>\n",
       "      <td>4574000.0</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0</td>\n",
       "      <td>25.5</td>\n",
       "      <td>25</td>\n",
       "      <td>28</td>\n",
       "      <td>436.0</td>\n",
       "      <td>215</td>\n",
       "      <td>27</td>\n",
       "      <td>30.0</td>\n",
       "      <td>31.5</td>\n",
       "      <td>-3.754444444444443</td>\n",
       "      <td>-0.062574074074074</td>\n",
       "      <td>-0.001042901234568</td>\n",
       "      <td>6771</td>\n",
       "      <td>11.740336777777779</td>\n",
       "      <td>16</td>\n",
       "      <td>92.599999999999994</td>\n",
       "      <td>74.587687829039027</td>\n",
       "      <td>0.002972468780996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2081976</th>\n",
       "      <td>3</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>24</td>\n",
       "      <td>9</td>\n",
       "      <td>55</td>\n",
       "      <td>21</td>\n",
       "      <td>915</td>\n",
       "      <td>2084000.0</td>\n",
       "      <td>5.257800000000000</td>\n",
       "      <td>0.006510000000000</td>\n",
       "      <td>0.008511277777778</td>\n",
       "      <td>0</td>\n",
       "      <td>14.5</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>-156.0</td>\n",
       "      <td>-38</td>\n",
       "      <td>11</td>\n",
       "      <td>13.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.086666666666665</td>\n",
       "      <td>-0.202185185185185</td>\n",
       "      <td>-0.005865354938272</td>\n",
       "      <td>5256</td>\n",
       "      <td>15.369914388888883</td>\n",
       "      <td>11</td>\n",
       "      <td>93.000000000000000</td>\n",
       "      <td>77.230002121155849</td>\n",
       "      <td>0.001017021056128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183515</th>\n",
       "      <td>1</td>\n",
       "      <td>2018</td>\n",
       "      <td>12</td>\n",
       "      <td>8</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>47</td>\n",
       "      <td>189</td>\n",
       "      <td>2652000.0</td>\n",
       "      <td>14.687500000000000</td>\n",
       "      <td>0.244791666666667</td>\n",
       "      <td>0.006371527777778</td>\n",
       "      <td>0</td>\n",
       "      <td>22.5</td>\n",
       "      <td>26</td>\n",
       "      <td>30</td>\n",
       "      <td>60.0</td>\n",
       "      <td>213</td>\n",
       "      <td>25</td>\n",
       "      <td>19.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>-2.539166666666665</td>\n",
       "      <td>-0.042319444444444</td>\n",
       "      <td>-0.001693904320988</td>\n",
       "      <td>2251</td>\n",
       "      <td>25.701540458333330</td>\n",
       "      <td>2</td>\n",
       "      <td>73.200000000000003</td>\n",
       "      <td>58.975534160689868</td>\n",
       "      <td>0.002560480023384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2315261</th>\n",
       "      <td>3</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>23</td>\n",
       "      <td>11</td>\n",
       "      <td>13</td>\n",
       "      <td>18</td>\n",
       "      <td>989</td>\n",
       "      <td>2029000.0</td>\n",
       "      <td>19.757800000000000</td>\n",
       "      <td>0.329296666666667</td>\n",
       "      <td>0.005488277777778</td>\n",
       "      <td>0</td>\n",
       "      <td>13.5</td>\n",
       "      <td>14</td>\n",
       "      <td>22</td>\n",
       "      <td>-170.0</td>\n",
       "      <td>-39</td>\n",
       "      <td>16</td>\n",
       "      <td>16.5</td>\n",
       "      <td>17.5</td>\n",
       "      <td>1.822777777777773</td>\n",
       "      <td>0.030379629629630</td>\n",
       "      <td>0.000506327160494</td>\n",
       "      <td>7617</td>\n",
       "      <td>25.324211083333331</td>\n",
       "      <td>11</td>\n",
       "      <td>59.299999999999997</td>\n",
       "      <td>41.357401281180941</td>\n",
       "      <td>0.001945806141727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1680099</th>\n",
       "      <td>2</td>\n",
       "      <td>2019</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>15</td>\n",
       "      <td>50</td>\n",
       "      <td>29</td>\n",
       "      <td>700</td>\n",
       "      <td>3596000.0</td>\n",
       "      <td>45.468800000000002</td>\n",
       "      <td>0.441146666666667</td>\n",
       "      <td>0.002074666666667</td>\n",
       "      <td>0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>27</td>\n",
       "      <td>31</td>\n",
       "      <td>-184.0</td>\n",
       "      <td>-37</td>\n",
       "      <td>29</td>\n",
       "      <td>35.0</td>\n",
       "      <td>36.5</td>\n",
       "      <td>-0.933055555555542</td>\n",
       "      <td>0.111041666666667</td>\n",
       "      <td>0.003960570987654</td>\n",
       "      <td>9411</td>\n",
       "      <td>22.135458305555559</td>\n",
       "      <td>7</td>\n",
       "      <td>91.500000000000000</td>\n",
       "      <td>71.579134713545329</td>\n",
       "      <td>0.002682532662561</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         IMEI  Year  Month  Day  Hour  Minute  Seconds    tp         sp  \\\n",
       "1108580     2  2018     12   25    14      50       57   492   788000.0   \n",
       "2395132     4  2018     11   17    16      54       26  1031    84000.0   \n",
       "3732533     5  2018     12   21    18      43       18  1579  2673000.0   \n",
       "1950630     3  2018     12   12     9       1       25   862   648000.0   \n",
       "699355      2  2018     11   30     7      12        3   360  1913000.0   \n",
       "3127675     4  2018     12   26    16      56       45  1289  4574000.0   \n",
       "2081976     3  2018     12   24     9      55       21   915  2084000.0   \n",
       "183515      1  2018     12    8    11       1       47   189  2652000.0   \n",
       "2315261     3  2019      1   23    11      13       18   989  2029000.0   \n",
       "1680099     2  2019      1   24    15      50       29   700  3596000.0   \n",
       "\n",
       "                      EVVSP        EVVSP_delta       EVVSP_change  EVGPO  \\\n",
       "1108580  13.687500000000000  0.228125000000000  0.003802083333333      0   \n",
       "2395132   0.000000000000000  0.000000000000000  0.000000000000000      0   \n",
       "3732533  51.359400000000001  0.059766666666667 -0.004754750000000      0   \n",
       "1950630  17.468800000000002  0.291146666666667  0.004852444444444      0   \n",
       "699355   53.890599999999999  0.750130000000000  0.012875444444444      0   \n",
       "3127675   0.000000000000000  0.000000000000000  0.000000000000000      0   \n",
       "2081976   5.257800000000000  0.006510000000000  0.008511277777778      0   \n",
       "183515   14.687500000000000  0.244791666666667  0.006371527777778      0   \n",
       "2315261  19.757800000000000  0.329296666666667  0.005488277777778      0   \n",
       "1680099  45.468800000000002  0.441146666666667  0.002074666666667      0   \n",
       "\n",
       "         EVOAS  EVIGM_Latest  EVCOM_Latest  EVACO_Z  EVIRT_Min  EVDI2  \\\n",
       "1108580   20.5            26            27   -128.0        -38     24   \n",
       "2395132   28.0            39            42  -1004.0        215     43   \n",
       "3732533   16.0            21            23    -30.0        213     22   \n",
       "1950630   19.0            15            16     -4.0        -38     15   \n",
       "699355    17.0            17            17   -108.0        -38     20   \n",
       "3127675   25.5            25            28    436.0        215     27   \n",
       "2081976   14.5            11            11   -156.0        -38     11   \n",
       "183515    22.5            26            30     60.0        213     25   \n",
       "2315261   13.5            14            22   -170.0        -39     16   \n",
       "1680099   20.0            27            31   -184.0        -37     29   \n",
       "\n",
       "         EVBMI_Latest  EVBMA_Latest              EVVAC        EVVAC_delta  \\\n",
       "1108580          30.0          31.5 -1.801111111111113 -0.030018518518519   \n",
       "2395132          30.0          31.5  0.000000000000000  0.000000000000000   \n",
       "3732533          18.0          19.0  3.515555555555553  0.077041666666667   \n",
       "1950630          16.5          17.5  0.217222222222229  0.003620370370370   \n",
       "699355           21.5          24.0  1.041666666666667  0.089699074074074   \n",
       "3127675          30.0          31.5 -3.754444444444443 -0.062574074074074   \n",
       "2081976          13.0          14.0  0.086666666666665 -0.202185185185185   \n",
       "183515           19.0          20.0 -2.539166666666665 -0.042319444444444   \n",
       "2315261          16.5          17.5  1.822777777777773  0.030379629629630   \n",
       "1680099          35.0          36.5 -0.933055555555542  0.111041666666667   \n",
       "\n",
       "              EVVAC_change  EVODO              EVODOH  Driver name  \\\n",
       "1108580 -0.000500308641975   5754   1.360880708333333            3   \n",
       "2395132  0.000000000000000   1891   0.000000000000000           13   \n",
       "3732533  0.003418055555556   2603  14.765663333333334           18   \n",
       "1950630  0.000060339506173   3613   1.358151055555555           11   \n",
       "699355   0.002061651234568   2778  13.167723305555556            3   \n",
       "3127675 -0.001042901234568   6771  11.740336777777779           16   \n",
       "2081976 -0.005865354938272   5256  15.369914388888883           11   \n",
       "183515  -0.001693904320988   2251  25.701540458333330            2   \n",
       "2315261  0.000506327160494   7617  25.324211083333331           11   \n",
       "1680099  0.003960570987654   9411  22.135458305555559            7   \n",
       "\n",
       "                   EVSMA_in          EVSMA_EWMA        EVSMA_delta  \n",
       "1108580  88.900000000000006  86.984514267030164  0.002864890407807  \n",
       "2395132  72.099999999999994  72.099999999999994  0.000000000000000  \n",
       "3732533  93.200000000000003  81.785821017636010  0.009688848055461  \n",
       "1950630  91.900000000000006  88.567376611587264  0.002283952935173  \n",
       "699355   92.000000000000000  79.504335189671053  0.003536786090535  \n",
       "3127675  92.599999999999994  74.587687829039027  0.002972468780996  \n",
       "2081976  93.000000000000000  77.230002121155849  0.001017021056128  \n",
       "183515   73.200000000000003  58.975534160689868  0.002560480023384  \n",
       "2315261  59.299999999999997  41.357401281180941  0.001945806141727  \n",
       "1680099  91.500000000000000  71.579134713545329  0.002682532662561  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[\"tp\"] = encoder.fit_transform(data[\"tp\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2118"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[\"tp\"].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "trainset_final = data[data[\"tp\"] < 1643]\n",
    "validset_final = data[data[\"tp\"].between(1643, 1743, inclusive = True)]\n",
    "testset_final = data[data[\"tp\"] > 1743]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Trainset = 3871645\n",
      "Validset = 224878\n",
      "Testset = 667516\n"
     ]
    }
   ],
   "source": [
    "print(\"Trainset = {}\".format(len(trainset_final)))\n",
    "print(\"Validset = {}\".format(len(validset_final)))\n",
    "print(\"Testset = {}\".format(len(testset_final)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Copying file://trainset_final.csv [Content-Type=text/csv]...\n",
      "==> NOTE: You are uploading one or more large file(s), which would run          \n",
      "significantly faster if you enable parallel composite uploads. This\n",
      "feature can be enabled by editing the\n",
      "\"parallel_composite_upload_threshold\" value in your .boto\n",
      "configuration file. However, note that if you do this large files will\n",
      "be uploaded as `composite objects\n",
      "<https://cloud.google.com/storage/docs/composite-objects>`_,which\n",
      "means that any user who downloads such objects will need to have a\n",
      "compiled crcmod installed (see \"gsutil help crcmod\"). This is because\n",
      "without a compiled crcmod, computing checksums on composite objects is\n",
      "so slow that gsutil disables downloads of composite objects.\n",
      "\n",
      "- [1 files][889.8 MiB/889.8 MiB]   26.5 MiB/s                                   \n",
      "Operation completed over 1 objects/889.8 MiB.                                    \n"
     ]
    }
   ],
   "source": [
    "# saving the trainset to Bucket\n",
    "trainset_final.to_csv('trainset_final.csv', index = False)\n",
    "!gsutil cp 'trainset_final.csv' 'gs://msil_raw/training_data/trainset_final.csv'\n",
    "%gcs read --object gs://msil_raw/training_data/trainset_final.csv --variable trainset_final\n",
    "df = pd.read_csv(BytesIO(trainset_final))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Copying file://validset_final.csv [Content-Type=text/csv]...\n",
      "\\ [1 files][ 49.5 MiB/ 49.5 MiB]                                                \n",
      "Operation completed over 1 objects/49.5 MiB.                                     \n"
     ]
    }
   ],
   "source": [
    "# saving the validset to Bucket\n",
    "validset_final.to_csv('validset_final.csv', index = False)\n",
    "!gsutil cp 'validset_final.csv' 'gs://msil_raw/training_data/validset_final.csv'\n",
    "%gcs read --object gs://msil_raw/training_data/validset_final.csv --variable validset_final\n",
    "df3 = pd.read_csv(BytesIO(validset_final))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Copying file://testset_final.csv [Content-Type=text/csv]...\n",
      "/ [1 files][141.7 MiB/141.7 MiB]                                                \n",
      "Operation completed over 1 objects/141.7 MiB.                                    \n"
     ]
    }
   ],
   "source": [
    "# saving the testset to Bucket\n",
    "testset_final.to_csv('testset_final.csv', index = False)\n",
    "!gsutil cp 'testset_final.csv' 'gs://msil_raw/training_data/testset_final.csv'\n",
    "%gcs read --object gs://msil_raw/training_data/testset_final.csv --variable testset_final\n",
    "df3 = pd.read_csv(BytesIO(testset_final))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "## Selecting Trips from testset to be used as benchmark for Model Performance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IMEI</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Day</th>\n",
       "      <th>Hour</th>\n",
       "      <th>Minute</th>\n",
       "      <th>Seconds</th>\n",
       "      <th>tp</th>\n",
       "      <th>sp</th>\n",
       "      <th>EVVSP</th>\n",
       "      <th>EVVSP_delta</th>\n",
       "      <th>EVVSP_change</th>\n",
       "      <th>EVGPO</th>\n",
       "      <th>EVOAS</th>\n",
       "      <th>EVIGM_Latest</th>\n",
       "      <th>EVCOM_Latest</th>\n",
       "      <th>EVACO_Z</th>\n",
       "      <th>EVIRT_Min</th>\n",
       "      <th>EVDI2</th>\n",
       "      <th>EVBMI_Latest</th>\n",
       "      <th>EVBMA_Latest</th>\n",
       "      <th>EVVAC</th>\n",
       "      <th>EVVAC_delta</th>\n",
       "      <th>EVVAC_change</th>\n",
       "      <th>EVODO</th>\n",
       "      <th>EVODOH</th>\n",
       "      <th>Driver name</th>\n",
       "      <th>EVSMA_in</th>\n",
       "      <th>EVSMA_EWMA</th>\n",
       "      <th>EVSMA_delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>26</td>\n",
       "      <td>18</td>\n",
       "      <td>53</td>\n",
       "      <td>20</td>\n",
       "      <td>1744</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>29</td>\n",
       "      <td>34</td>\n",
       "      <td>100.0</td>\n",
       "      <td>213</td>\n",
       "      <td>34</td>\n",
       "      <td>22.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1110</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23</td>\n",
       "      <td>82.299999999999997</td>\n",
       "      <td>82.299999999999997</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>26</td>\n",
       "      <td>18</td>\n",
       "      <td>53</td>\n",
       "      <td>21</td>\n",
       "      <td>1744</td>\n",
       "      <td>1000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>29</td>\n",
       "      <td>34</td>\n",
       "      <td>95.0</td>\n",
       "      <td>213</td>\n",
       "      <td>34</td>\n",
       "      <td>22.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1110</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23</td>\n",
       "      <td>82.299999999999997</td>\n",
       "      <td>82.299999999999997</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>26</td>\n",
       "      <td>18</td>\n",
       "      <td>53</td>\n",
       "      <td>22</td>\n",
       "      <td>1744</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>29</td>\n",
       "      <td>34</td>\n",
       "      <td>92.0</td>\n",
       "      <td>213</td>\n",
       "      <td>34</td>\n",
       "      <td>22.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1110</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23</td>\n",
       "      <td>82.299999999999997</td>\n",
       "      <td>82.299999999999997</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>26</td>\n",
       "      <td>18</td>\n",
       "      <td>53</td>\n",
       "      <td>23</td>\n",
       "      <td>1744</td>\n",
       "      <td>3000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>29</td>\n",
       "      <td>34</td>\n",
       "      <td>103.0</td>\n",
       "      <td>213</td>\n",
       "      <td>34</td>\n",
       "      <td>22.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1110</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23</td>\n",
       "      <td>82.299999999999997</td>\n",
       "      <td>82.299999999999997</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>2018</td>\n",
       "      <td>11</td>\n",
       "      <td>26</td>\n",
       "      <td>18</td>\n",
       "      <td>53</td>\n",
       "      <td>24</td>\n",
       "      <td>1744</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>21.5</td>\n",
       "      <td>29</td>\n",
       "      <td>34</td>\n",
       "      <td>96.0</td>\n",
       "      <td>213</td>\n",
       "      <td>34</td>\n",
       "      <td>22.5</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1110</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23</td>\n",
       "      <td>82.299999999999997</td>\n",
       "      <td>82.299999999999997</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   IMEI  Year  Month  Day  Hour  Minute  Seconds    tp      sp  EVVSP  \\\n",
       "0     7  2018     11   26    18      53       20  1744     0.0    0.0   \n",
       "1     7  2018     11   26    18      53       21  1744  1000.0    0.0   \n",
       "2     7  2018     11   26    18      53       22  1744  2000.0    0.0   \n",
       "3     7  2018     11   26    18      53       23  1744  3000.0    0.0   \n",
       "4     7  2018     11   26    18      53       24  1744  4000.0    0.0   \n",
       "\n",
       "   EVVSP_delta  EVVSP_change  EVGPO  EVOAS  EVIGM_Latest  EVCOM_Latest  \\\n",
       "0          0.0           0.0      0   21.5            29            34   \n",
       "1          0.0           0.0      0   21.5            29            34   \n",
       "2          0.0           0.0      0   21.5            29            34   \n",
       "3          0.0           0.0      0   21.5            29            34   \n",
       "4          0.0           0.0      0   21.5            29            34   \n",
       "\n",
       "   EVACO_Z  EVIRT_Min  EVDI2  EVBMI_Latest  EVBMA_Latest  EVVAC  EVVAC_delta  \\\n",
       "0    100.0        213     34          22.5          23.0    0.0          0.0   \n",
       "1     95.0        213     34          22.5          23.0    0.0          0.0   \n",
       "2     92.0        213     34          22.5          23.0    0.0          0.0   \n",
       "3    103.0        213     34          22.5          23.0    0.0          0.0   \n",
       "4     96.0        213     34          22.5          23.0    0.0          0.0   \n",
       "\n",
       "   EVVAC_change  EVODO  EVODOH  Driver name            EVSMA_in  \\\n",
       "0           0.0   1110     0.0           23  82.299999999999997   \n",
       "1           0.0   1110     0.0           23  82.299999999999997   \n",
       "2           0.0   1110     0.0           23  82.299999999999997   \n",
       "3           0.0   1110     0.0           23  82.299999999999997   \n",
       "4           0.0   1110     0.0           23  82.299999999999997   \n",
       "\n",
       "           EVSMA_EWMA  EVSMA_delta  \n",
       "0  82.299999999999997          0.0  \n",
       "1  82.299999999999997          0.0  \n",
       "2  82.299999999999997          0.0  \n",
       "3  82.299999999999997          0.0  \n",
       "4  82.299999999999997          0.0  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mybucket = storage.Bucket(BUCKET_NAME)\n",
    "data_csv = mybucket.object(FOLDER_NAME + \"/\" + \"testset_final.csv\")\n",
    "\n",
    "uri = data_csv.uri\n",
    "%gcs read --object $uri --variable data\n",
    "\n",
    "testset = pd.read_csv(BytesIO(data))\n",
    "testset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Trip</th>\n",
       "      <th>VSP</th>\n",
       "      <th>VAC</th>\n",
       "      <th>OAS</th>\n",
       "      <th>ODOH</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>2018</td>\n",
       "      <td>25.122161666666667</td>\n",
       "      <td>0.000000000000000e+00</td>\n",
       "      <td>19.132777777777779</td>\n",
       "      <td>12.551648979027775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1758</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0.000000000000000e+00</td>\n",
       "      <td>23.394736842105264</td>\n",
       "      <td>0.000000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>348</th>\n",
       "      <td>2118</td>\n",
       "      <td>24.395757549019610</td>\n",
       "      <td>6.501698235713335e-17</td>\n",
       "      <td>16.411764705882351</td>\n",
       "      <td>20.736393916666664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>1869</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0.000000000000000e+00</td>\n",
       "      <td>28.500000000000000</td>\n",
       "      <td>0.000000000000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251</th>\n",
       "      <td>1989</td>\n",
       "      <td>0.000000000000000</td>\n",
       "      <td>0.000000000000000e+00</td>\n",
       "      <td>16.500000000000000</td>\n",
       "      <td>0.000000000000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Trip                 VSP                    VAC                 OAS  \\\n",
       "229  2018  25.122161666666667  0.000000000000000e+00  19.132777777777779   \n",
       "14   1758   0.000000000000000  0.000000000000000e+00  23.394736842105264   \n",
       "348  2118  24.395757549019610  6.501698235713335e-17  16.411764705882351   \n",
       "125  1869   0.000000000000000  0.000000000000000e+00  28.500000000000000   \n",
       "251  1989   0.000000000000000  0.000000000000000e+00  16.500000000000000   \n",
       "\n",
       "                   ODOH  \n",
       "229  12.551648979027775  \n",
       "14    0.000000000000000  \n",
       "348  20.736393916666664  \n",
       "125   0.000000000000000  \n",
       "251   0.000000000000000  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "VSP, VAC, ODOH, OAS, trip_number = [], [], [], [], []\n",
    "for i in list(testset[\"tp\"].unique()):\n",
    "  temp_df = testset[testset[\"tp\"] == i]\n",
    "  tp_EVVSP_mean = temp_df[\"EVVSP\"].mean()\n",
    "  tp_EVVAC_mean = temp_df[\"EVVAC\"].mean()\n",
    "  tp_EVODOH_last_reading = temp_df[\"EVODOH\"].iloc[-1]\n",
    "  tp_EVOAS_mean = temp_df[\"EVOAS\"].mean()\n",
    "  tp = i\n",
    "  trip_number.append(tp)\n",
    "  VSP.append(tp_EVVSP_mean)\n",
    "  VAC.append(tp_EVVAC_mean)\n",
    "  ODOH.append(tp_EVODOH_last_reading)\n",
    "  OAS.append(tp_EVOAS_mean)\n",
    "\n",
    "perf_df = pd.DataFrame({\n",
    "  \"Trip\": trip_number,\n",
    "  \"VSP\": VSP,\n",
    "  \"VAC\": VAC,\n",
    "  \"ODOH\": ODOH,\n",
    "  \"OAS\": OAS\n",
    "})\n",
    "perf_df = perf_df[[\"Trip\", \"VSP\", \"VAC\", \"OAS\", \"ODOH\"]]\n",
    "perf_df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "perf_df = perf_df[perf_df[\"ODOH\"] > 5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Max velocity = 57    1780\n",
      "Name: Trip, dtype: int64\n",
      "Min velocity = 22    1814\n",
      "Name: Trip, dtype: int64\n",
      "Max acc = 217    1936\n",
      "Name: Trip, dtype: int64\n",
      "Min acc = 235    1973\n",
      "Name: Trip, dtype: int64\n",
      "Max OAS = 13    1757\n",
      "Name: Trip, dtype: int64\n",
      "Min OAS = 218    1937\n",
      "Name: Trip, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(\"Max velocity = {}\".format(perf_df[perf_df[\"VSP\"] == perf_df[\"VSP\"].max()][\"Trip\"]))\n",
    "print(\"Min velocity = {}\".format(perf_df[perf_df[\"VSP\"] == perf_df[\"VSP\"].min()][\"Trip\"]))\n",
    "print(\"Max acc = {}\".format(perf_df[perf_df[\"VAC\"] == perf_df[\"VAC\"].max()][\"Trip\"]))\n",
    "print(\"Min acc = {}\".format(perf_df[perf_df[\"VAC\"] == perf_df[\"VAC\"].min()][\"Trip\"]))\n",
    "print(\"Max OAS = {}\".format(perf_df[perf_df[\"OAS\"] == perf_df[\"OAS\"].max()][\"Trip\"]))\n",
    "print(\"Min OAS = {}\".format(perf_df[perf_df[\"OAS\"] == perf_df[\"OAS\"].min()][\"Trip\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Trips on which performance of models need to be checked\n",
    "\n",
    "| Specification | Trip Number | ODOH |\n",
    "|---------------|-------------|------|\n",
    "| VSP (max)     |    1780     | 60.5 |\n",
    "| VSP (min)     |    1814     | 22.5 |\n",
    "| VAC (max)     |    1936     | 6.32 |\n",
    "| VAC (min)     |    1973     | 13.9 |\n",
    "| OAS (max)     |    1757     | 27.7 |\n",
    "| OAS (min)     |    1937     | 13.9 |\n",
    "| Random 01     |    1889     | 8.4  |\n",
    "| Random 02     |    2018     | 12.5 |\n",
    "| Random 03     |    2011     | 10.1 |\n",
    "| Random 04     |    1947     | 7.56 |\n",
    "| Random 05     |    1860     | 19.8 |"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
